package com.infore.siteManagement.repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.EntityGraph;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.infore.siteManagement.entity.Station;
/**
 * 站点仓库
 * @author 刘乐法
 *
 *
 */
@Repository
public interface IStationRepository extends JpaRepository<Station, String>,JpaSpecificationExecutor<Station>{

	@Modifying
	@Transactional
	int deleteByStationIdIn(List<String> ids);
	
	/** 插入相关站点数据 **/
	@Modifying
	@Transactional
	@Query(value = "INSERT INTO STATION(STATION_ID,STATION_MN,STATION_NAME,AREA_ID,BASINN_ID,CONTROL_LEVEL,LONGITUDE,LATITUDE,STATION_ADDRESS,STATION_TYPE1,TARGER_WATER_QUALITY,MONITOR_STATUS,ENABLED,NETWORKING,CITY,CITY_NAME,AREA_NAMES)"
			+ " VALUES (:stationId,:stationCode,:stationName,:areaId,:basinId,:controlorLevel,:longitude,:latitude,:stationAddress,:stationType,:targetLevel,:monitorStatus,:enabled,:networking,:city,:cityName,:areaName)", nativeQuery = true)
	int insertStationInfo(@Param("stationId") String stationId, @Param("stationCode") String stationCode,
			@Param("stationName") String stationName, @Param("areaId") String areaId, @Param("basinId") String basinId,
			@Param("controlorLevel") Integer controlorLevel, @Param("longitude") Double longitude,
			@Param("latitude") Double latitude, @Param("stationAddress") String stationAddress,
			@Param("stationType") String stationType, @Param("targetLevel") String targetLevel,
			@Param("monitorStatus") String monitorStatus, @Param("enabled") String enabled, @Param("networking") String networking,
			@Param("city") String city, @Param("cityName") String cityName, @Param("areaName") String areaName);

	/** 更新相关站点数据 **/
	@Modifying
	@Transactional
	@Query(value = "UPDATE STATION SET STATION_MN = :stationCode, "
			+ "STATION_NAME = :stationName,AREA_ID = :areaId, BASINN_ID = :basinId,CONTROL_LEVEL = :controlorLevel,"
			+ "LONGITUDE = :longitude, LATITUDE = :latitude, STATION_ADDRESS = :stationAddress, "
			+ "STATION_TYPE1 = :stationType, TARGER_WATER_QUALITY = :targetLevel, CITY = :city, CITY_NAME = :cityName, AREA_NAMES = :areaName WHERE STATION_ID = :stationId", nativeQuery = true)
	int updateStationInfo(@Param("stationCode") String stationCode, @Param("stationName") String stationName,
			@Param("areaId") String areaId, @Param("basinId") String basinId,
			@Param("controlorLevel") Integer controlorLevel, @Param("longitude") Double longitude,
			@Param("latitude") Double latitude, @Param("stationAddress") String stationAddress,
			@Param("stationType") String stationType, @Param("targetLevel") String targetLevel,
			@Param("city") String city, @Param("cityName") String cityName, @Param("areaName") String areaName,
			@Param("stationId") String stationId);
	
	/** 查询相关站点数据 **/
	@Query(value = "SELECT STATION_ID,STATION_MN,STATION_NAME,CONTROL_LEVEL,LONGITUDE,LATITUDE,STATION_ADDRESS"
			+ ",STATION_TYPE1,TARGER_WATER_QUALITY,CITY,CITY_NAME,AREA_ID,AREA_NAMES FROM STATION WHERE STATION_MN = :stationCode", nativeQuery = true)
	List<Map<String, Object>> selectStationInfo(@Param("stationCode") String stationCode);
	
	//查询站点图片外景
	@Query(value = "select t.out_image from STATION t  where t.station_mn = :stationCode", nativeQuery = true)
	String selectOutImageInfo(@Param("stationCode") String stationCode);
	
	//查询站点图片内景
	@Query(value = "select t.inside_image from STATION t  where t.station_mn = :stationCode", nativeQuery = true)
	String selectInsideImageInfo(@Param("stationCode") String stationCode);
	
	//查询站点图片建设
	@Query(value = "select t.build_image from STATION t  where t.station_mn = :stationCode", nativeQuery = true)
	String selectBuildImageInfo(@Param("stationCode") String stationCode);
	
	//查询站点下所有图片
	@Query(value = "select t.out_image,t.inside_image,t.build_image from STATION t where t.station_mn = :stationCode", nativeQuery = true)
	List<Map<String, Object>> selectAllImage(@Param("stationCode") String stationCode);
	
	//查询站点下所有图片----用于删除
	@Query(value = "select t.out_image,t.inside_image,t.build_image from STATION t where t.station_id in (:ids)", nativeQuery = true)
	List<Map<String, Object>> selectAllImageByStationId(@Param("ids") List<String> ids);
	
	//更新站点图片外景
	@Modifying
	@Transactional
	@Query(value = "update STATION t set t.out_image = :imageName where t.station_mn = :mn ", nativeQuery = true)
	int updateImageInfo(@Param("mn") String mn,
			@Param("imageName") String imageName);
	
	//更新站点图片内景
	@Modifying
	@Transactional
	@Query(value = "update STATION t set t.inside_image = :imageName where t.station_mn = :mn ", nativeQuery = true)
	int updateImageInside(@Param("mn") String mn,
			@Param("imageName") String imageName);
	
	//更新站点图片建设
	@Modifying
	@Transactional
	@Query(value = "update STATION t set t.build_image = :imageName where t.station_mn = :mn ", nativeQuery = true)
	int updateImageBuild(@Param("mn") String mn,
			@Param("imageName") String imageName);
	
	//通过查询信息的到站点编码
	@Query(value = "select t.station_mn  from STATION t where t.station_name in (:stationNamesList) and t.area_id in (:areaCodeList) and t.basinn_id in (:basinCodeList) and t.station_type_id in (:stationTypeList) and t.station_type1 in (:stationType1List)" ,nativeQuery = true)
	List<String> findStationMnByInfo(@Param("areaCodeList") List<String> areaCodeList ,
			@Param("basinCodeList") List<String> basinCodeList ,
			@Param("stationTypeList") List<String> stationTypeList ,
			@Param("stationType1List") List<String> stationType1List ,
			@Param("stationNamesList") List<String> stationNamesList );
	
//	//
	//@EntityGraph(value = "station_view")根据区域id和站点名模糊查询
//	@Query(value = "select * from station t where 1=1 and (t.area_id = :areaId or nvl (:areaId,'1')='1') and (t.station_name like :stationName or nvl (:stationName,'1')='1') order by t.sort asc ", nativeQuery = true)
//	List<Station> selectStationByAreaIdAndStationName(@Param("areaId") String areaId,@Param("stationName") String stationName);
	
	//插入站点完整信息
	@Modifying
	@Transactional
	@Query(value = "INSERT INTO STATION (AREA_ID,AREA_NAMES,BASINN_ID,BASINN_NAME,"
			+ "CITY_ID,CITY_NAME,CONTROL_LEVEL,ENABLED,ENABLED_TIME,LATITUDE,"
			+ "LOCAL_MANGERMNET_UNITS,LONGITUDE,PROVINCE_ID,PROVINCE_NAME,"
			+ "SORT,STATION_ADDRESS,STATION_MN,STATION_ID,STATION_NAME,"
			+ "STATION_PASSWORLD,STATION_PROPERTY,STATION_PROVINCES_UP_DOWN,"
			+ "STATION_TYPE_ID,STATION_CLASSIFY_ID,TARGER_WATER_QUALITY,STATION_RIVER,"
			+ "STATION_FUNCTION,OPER_COMPANY_ID,OPER_COMPANY_NAME,STATION_BRIEF_INFO,"
			+ "PERIPHERAL_HISTORY,SUIFACE_WATER_TYPES,data_frequency,station_type1,KEY_PERSON,"
			+ "is_special_business,kil,station_level,station_leader_id,station_leader,"
			+ "opcen_id,opcen_name) " + 
			" VALUES (:areaid,:areaName,:basinId,:basinName,:city,:cityName,:controlLevel,"
			+ ":enabled,:enabledTimeDate,:latitudeFloat,:localManagementUnits,:longitudeFloat,"
			+ ":provinceId,:provinceName,:sort,:stationAddress,:stationCode,:stationId,:stationName,"
			+ ":stationPassworld,:stationProperty,:stationProvincesUpDown,:stationType,:stationClassify,"
			+ ":targetLevel,:stationRiver,:stationFunction,:operCompanyId,:operCompanyName,:stationBriefInfo,"
			+ ":peripheralHistory,:surfaceWaterTypes,:dataFrequency,:stationType1,:keyPerson,:isSpecialBusiness,"
			+ ":kil,:stationLevel,:stationLeaderId,:stationLeader,:opcenId,:opcenName)",nativeQuery = true)
	int insertStationAllInfo(@Param("areaid") String areaid ,@Param("areaName") String areaName ,@Param("basinId") String basinId ,
			@Param("basinName") String basinName ,@Param("city") String city ,@Param("cityName") String cityName ,
			@Param("controlLevel") String controlLevel ,@Param("enabled") String enabled ,@Param("enabledTimeDate") Date enabledTimeDate ,
			@Param("latitudeFloat") Float latitudeFloat ,@Param("localManagementUnits") String localManagementUnits ,@Param("longitudeFloat") Float longitudeFloat ,
			@Param("provinceId") String provinceId ,@Param("provinceName") String provinceName ,@Param("sort") String sort ,
			@Param("stationAddress") String stationAddress ,@Param("stationCode") String stationCode ,@Param("stationId") String stationId ,
			@Param("stationName") String stationName ,@Param("stationPassworld") String stationPassworld ,@Param("stationProperty") String stationProperty ,
			@Param("stationProvincesUpDown") String stationProvincesUpDown ,@Param("stationType") String stationType ,@Param("stationClassify") String stationClassify ,
			@Param("targetLevel") String targetLevel,@Param("stationRiver") String stationRiver,@Param("stationFunction") String stationFunction,@Param("operCompanyId") String operCompanyId,@Param("operCompanyName") String operCompanyName ,
			@Param("stationBriefInfo") String stationBriefInfo ,@Param("peripheralHistory") String peripheralHistory,@Param("surfaceWaterTypes") String surfaceWaterTypes ,
			@Param("dataFrequency") String dataFrequency,@Param("stationType1") String stationType1,@Param("keyPerson") String keyPerson,
			@Param("isSpecialBusiness") String isSpecialBusiness,@Param("kil") String kil,@Param("stationLevel") String stationLevel,
			@Param("stationLeaderId") String stationLeaderId,@Param("stationLeader") String stationLeader,
			@Param("opcenId")String opcenId,@Param("opcenName") String opcenName);
	
	//更新站点完整信息
	@Modifying
	@Transactional
	@Query(value = "UPDATE STATION T SET " + 
			"AREA_ID=:areaid,AREA_NAMES=:areaName,BASINN_ID=:basinId,BASINN_NAME=:basinName,"
			+ "CITY_ID=:city,CITY_NAME=:cityName,CONTROL_LEVEL=:controlLevel,ENABLED=:enabled,"
			+ "ENABLED_TIME=:enabledTimeDate,LATITUDE=:latitudeFloat,LOCAL_MANGERMNET_UNITS=:localManagementUnits,"
			+ "LONGITUDE=:longitudeFloat,PROVINCE_ID=:provinceId,PROVINCE_NAME=:provinceName,SORT=:sort,"
			+ "STATION_ADDRESS=:stationAddress,STATION_MN=:stationCode,STATION_NAME=:stationName,"
			+ "STATION_PASSWORLD=:stationPassworld,STATION_PROPERTY=:stationProperty,STATION_PROVINCES_UP_DOWN=:stationProvincesUpDown,"
			+ "STATION_TYPE_ID=:stationType,STATION_CLASSIFY_ID=:stationClassify,TARGER_WATER_QUALITY=:targetLevel,"
			+ "STATION_RIVER=:stationRiver,STATION_FUNCTION=:stationFunction,OPER_COMPANY_ID=:operCompanyId,"
			+ "OPER_COMPANY_NAME=:operCompanyName,STATION_BRIEF_INFO=:stationBriefInfo,PERIPHERAL_HISTORY=:peripheralHistory,"
			+ "SUIFACE_WATER_TYPES=:surfaceWaterTypes,data_frequency=:dataFrequency,station_type1=:stationType1,"
			+ "KEY_PERSON=:keyPerson,is_special_business=:isSpecialBusiness,kil=:kil,station_level=:stationLevel,"
			+ "station_leader_id=:stationLeaderId,station_leader=:stationLeader,opcen_id=:opcenId,opcen_name=:opcenName " + 
			" WHERE STATION_ID=:stationId ",nativeQuery = true)
	int updateStationAllInfo(@Param("areaid") String areaid ,@Param("areaName") String areaName ,@Param("basinId") String basinId ,
			@Param("basinName") String basinName ,@Param("city") String city ,@Param("cityName") String cityName ,
			@Param("controlLevel") String controlLevel ,@Param("enabled") String enabled ,@Param("enabledTimeDate") Date enabledTimeDate ,
			@Param("latitudeFloat") Float latitudeFloat ,@Param("localManagementUnits") String localManagementUnits ,@Param("longitudeFloat") Float longitudeFloat ,
			@Param("provinceId") String provinceId ,@Param("provinceName") String provinceName ,@Param("sort") String sort ,
			@Param("stationAddress") String stationAddress ,@Param("stationCode") String stationCode ,@Param("stationId") String stationId ,
			@Param("stationName") String stationName ,@Param("stationPassworld") String stationPassworld ,@Param("stationProperty") String stationProperty ,
			@Param("stationProvincesUpDown") String stationProvincesUpDown ,@Param("stationType") String stationType ,@Param("stationClassify") String stationClassify ,
			@Param("targetLevel") String targetLevel ,@Param("stationRiver") String stationRiver,@Param("stationFunction") String stationFunction, @Param("operCompanyId") String operCompanyId,@Param("operCompanyName") String operCompanyName,
			@Param("stationBriefInfo") String stationBriefInfo ,@Param("peripheralHistory") String peripheralHistory,@Param("surfaceWaterTypes") String surfaceWaterTypes,
			@Param("dataFrequency") String dataFrequency,@Param("stationType1") String stationType1,@Param("keyPerson") String keyPerson,
			@Param("isSpecialBusiness") String isSpecialBusiness,@Param("kil") String kil,@Param("stationLevel") String stationLevel,
			@Param("stationLeaderId") String stationLeaderId,@Param("stationLeader") String stationLeader,
			@Param("opcenId")String opcenId,@Param("opcenName") String opcenName);

	/**自动站多站得到站点名称**/
	@Query(value = "select t.station_mn as MN ,t.station_name as NAME from STATION t where t.station_mn in(:mn)",nativeQuery = true)
	List<Map<String, Object>> queryStationNameByMn(@Param("mn")List<String> mn);
	
	/**区域内的所有站点部分信息---流域名称,站点名称，河流，mn，区域名称，目标等级**/
	@Query(value = "select s.station_id as STATION_ID, s.station_name as STATIONNAME,a.area_name AS AREANAME,s.targer_water_quality as ARMLEVEL,s.station_river as RIVER,s.station_mn as MN,s.data_frequency as FREQUENCY  from Station s " + 
			"left join AREA a on s.area_id = a.area_id " + 
			"where s.area_id in (" + 
			"select t.area_id from AREA t start with t.area_code in (:areaCode) connect by prior t.area_code=t.parent_id)",nativeQuery = true)
	List<Map<String, Object>> queryStationInfoByFatherArea(@Param("areaCode")List<String> areaCode);
	
	/**站点部分信息---流域名称,站点名称，河流，mn，区域名称，目标等级**/
	@Query(value = "select s.station_id as STATION_ID, s.station_name as STATIONNAME,"
			+ "s.targer_water_quality as ARMLEVEL,s.station_river as RIVER,s.station_mn as MN,"
			+ "s.data_frequency as FREQUENCY  from Station s " + 
			"where station_mn in (:mns)",nativeQuery = true)
	List<Map<String, Object>> queryStationInfoByMns(@Param("mns")List<String> mns);
	
	/**流域内的所有站点**/
	@Query(value = "select s.station_id as STATION_ID,s.station_name as STATIONNAME,b.basinn_name AS BASINNNAME,s.targer_water_quality as ARMLEVEL,s.station_river as RIVER,s.station_mn as MN,s.data_frequency as FREQUENCY  from Station s " + 
			"left join Basinn b on s.basinn_id = b.basinn_id where s.basinn_id in ( " + 
			"select n.basinn_id from  Basinn n where n.basinn_code in (:basinnCode) )",nativeQuery = true)
	List<Map<String, Object>> queryStationInfoByFatherBasinn(@Param("basinnCode")List<String> basinnCode);
		
	@EntityGraph(value = "station_view")
	List<Station> findAll(Specification<Station> spec);
	
	@EntityGraph(value = "station_view")
	List<Station> findAll();
	
	@EntityGraph(value = "station_view")
	Page<Station> findAll(Pageable pageable);
	
	@EntityGraph(value = "station_view")
	Page<Station> findAll(Specification<Station> spec,Pageable pageable);
	
	@EntityGraph(value = "station_view")
	List<Station> findAllByStationIdIn(List<String> id);
	
	@EntityGraph(value = "station_view")
	Station  findOneByStationId(String stationId);
	
	@EntityGraph(value = "station_view")
    Station  findOneByStationMn(String mn);

	@EntityGraph(value = "station_view")
	List<Station> findAllByStationMnIn(List<String> stationMnList);
	
	/**查询站点基础信息，包含区域名称，流域名称，不含省份城市**/
	@Query(value="select t.TARGER_WATER_QUALITY,t.station_name,t.longitude,t.latitude,t.station_address,t.build_time,t.station_leader,t.leader_tel,t.monitor_status,t.enabled,t.sort, " + 
			"t.control_level,t.station_id,t.auto_flag,t.manuel_flag,t.public_flag,t.oper_company_name,t.oper_company_leader,t.oper_company_leader_tel,t.station_leader_id, " + 
			"t.networking,t.station_mn,t.station_passworld,t.station_property,t.station_provinces_up_down,t.station_type1,t.user_id,t.enabled_time, " + 
			"t.local_mangermnet_units,t.station_river,t.station_function,t.station_brief_info,t.suiface_water_types,t.oper_company_id,t.data_frequency, " + 
			"a.area_code,a.area_name,a.aim_water_level as area_aim,a.area_id,a.parent_id,a.area_level_id,b.basinn_id,b.basinn_name,b.basinn_code,b.aim_water_level as basinn_aim, " + 
			"ty.station_type_id,ty.station_type_name,ty.station_type_code,cla.station_classify_id,cla.station_classify_name,cla.station_classify_code " + 
			"from station t left join area a on t.area_id = a.area_id left join basinn b on t.basinn_id = b.basinn_id left join station_type ty on t.station_type_id=ty.station_type_id " + 
			"left join station_classify cla on t.station_classify_id = cla.station_classify_id "+
			"where t.station_id in (:stationIds)",nativeQuery=true)
	List<Map<String, Object>> getStationBasicById(@Param("stationIds")List<String> stationIds);
	
	/**查询站点基础信息，包含区域名称，流域名称，不含省份城市**/
	@Query(value="select t.TARGER_WATER_QUALITY,t.station_name,t.longitude,t.latitude,t.station_address,t.build_time,t.station_leader,t.leader_tel,t.monitor_status,t.enabled,t.sort, " + 
			"t.control_level,t.station_id,t.auto_flag,t.manuel_flag,t.public_flag,t.oper_company_name,t.oper_company_leader,t.oper_company_leader_tel,t.station_leader_id, " + 
			"t.networking,t.station_mn,t.station_passworld,t.station_property,t.station_provinces_up_down,t.station_type1,t.user_id,t.enabled_time, " + 
			"t.local_mangermnet_units,t.station_river,t.station_function,t.station_brief_info,t.suiface_water_types,t.oper_company_id,t.data_frequency, " + 
			"a.area_code,a.area_name,a.aim_water_level as area_aim,a.area_id,a.parent_id,a.area_level_id,b.basinn_id,b.basinn_name,b.basinn_code,b.aim_water_level as basinn_aim, " + 
			"ty.station_type_id,ty.station_type_name,ty.station_type_code,cla.station_classify_id,cla.station_classify_name,cla.station_classify_code " + 
			"from station t left join area a on t.area_id = a.area_id left join basinn b on t.basinn_id = b.basinn_id left join station_type ty on t.station_type_id=ty.station_type_id " + 
			"left join station_classify cla on t.station_classify_id = cla.station_classify_id",nativeQuery=true)
	List<Map<String, Object>> getStationBasicAll();
	
	/**添加合同时查询站点和运维公司负责人部分信息，通过stationId得到stationMn**/
	@Query(value="select station_name,oper_company_name ,oper_company_id ,station_mn ,station_leader,station_leader_id from station where station_id in (:ids)",nativeQuery=true)
	List<Map<String, Object>> getStationMnBasicById(@Param("ids")List<String> ids);
	
	/**备品备件查询仪器名称站点编号**/
	@Query(value="select t.id as EQUIPMENT_SAMPLE_ID,s.station_name, s.station_id, m.equipment_name, s.station_mn, t.equip_model " + 
			"from station s left join EQUIPMENT_SAMPLE t on s.station_mn = t.station_mn " + 
			"left join equipment_model m on t.equipment_model_id = m.id " + 
			"where s.station_id in (:stationIds)",nativeQuery=true)
	List<Map<String, Object>> queryBystauionId(@Param("stationIds")List<String> stationIds);
	
	/**通过运维分公司筛选**/
	@Query(value="select station_id from station where OPER_COMPANY_ID = :companyId",nativeQuery =true)
	List<String> queryIdByCompanyId(@Param("companyId")String companyId);
	
	/**通过站点负责人筛选**/
	@Query(value="select station_id from station where STATION_LEADER_ID = :userId",nativeQuery =true)
	List<String> queryIdByLeaderId(@Param("userId")String userId);
	
	/**通过运维中心筛选**/
	@Query(value="select station_id from station where opcen_id = :opcenId",nativeQuery =true)
	List<String> queryIdByOpcenId(@Param("opcenId")String opcenId);
}

